package com.emp.database;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import DataBase.DataBase;

/*使用PrepareStatement对象实现DML操作
 * 优点：1.可以将sql语句发送到数据库进行预编译，提高程序执行的性能
 * 2.可以使用占位符操作可以避免程序中的dml语句因字符串拼接操作而带来的sql注入问题 
 */

public class LoginOperate extends DataBase{
	
	PreparedStatement ps = null;
		//向用户表中插入记录
	public void addUser(int id,String username,String password,Date udate,String photo){
		
		getcon();
		Blob blob = null;
		String sql = "insert into login values(?,?,?,?,?)";
		 try {
			blob = con.createBlob();
			FileInputStream fis = new FileInputStream(photo);
			OutputStream os = blob.setBinaryStream(1);
			byte [] b = new byte[1024];
			int i=0;
			while((i=fis.read(b))!=-1){
				os.write(b,0,i);
			}
		} catch (FileNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			ps=con.prepareStatement(sql);
			//给占位符赋值
			ps.setInt(1, id);
			ps.setString(2, username);
			ps.setString(3, password);
			ps.setDate(4, udate);
			ps.setBlob(5, blob);
			int flag = ps.executeUpdate();
			System.out.println("影响的行数"+flag);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				ps.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
	Statement sta = null;
	ResultSet rs = null;
	int flag = 0;//0代表登录失败，1代表登录成功
	public int checkLogin(String username,String password){
		String regex = "\\w+";
		if(!username.matches(regex)){
			
			return 0;
		}
		getcon();
		String sql = "select * from login where username='"+username+"' and password='"+password+"'";
		try {
			sta=con.createStatement();
			rs= sta.executeQuery(sql);
			while(rs.next()){
				flag = 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				sta.close();
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	
	public static void main(String[] args) {
		LoginOperate lo = new LoginOperate();
		lo.addUser(1001, "哦啦啦", "11111", Date.valueOf("2017-04-14"), "e://a.png");
		int flag = lo.checkLogin("111", "2222");
		if(flag == 0){
			System.out.println("登录失败");
		}else{
			System.out.println("登录成功");
		}
	}
}
